import flask
import sqlite3
import json


# 学生数据库
class StudentDB:
    def openDB(self):
        self.con = sqlite3.connect("students0404.db")
        self.cursor = self.con.cursor()

    def closeDB(self):
        self.con.commit()
        self.con.close()

    def initTable(self):
        res = {}
        try:
            self.cursor.execute(
                "create table students (No varchar(16) primary key,Name varchar(16), Sex varchar(8), Age int , IP  varchar(20))")
            res["msg"] = "OK"
        except Exception as err:
            res["msg"] = str(err)
        return res

    def insertRow(self, No, Name, Sex, Age, IP):
        res = {}
        try:
            self.cursor.execute("insert into students (No,Name,Sex,Age,IP) values (?,?,?,?,?)",
                                (No, Name, Sex, Age, IP))
            res["msg"] = "OK"
        except Exception as err:
            res["msg"] = str(err)
        return res

    def deleteRow(self, No):
        res = {}
        try:
            self.cursor.execute("delete from students where No=?", (No,))
            res["msg"] = "OK"
        except Exception as err:
            res["msg"] = str(err)
        return res

    def selectRows(self):
        res = {}
        try:
            data = []
            self.cursor.execute("select * from students order by No")
            rows = self.cursor.fetchall()
            for row in rows:
                d = {}
                d["No"] = row[0]
                d["Name"] = row[1]
                d["Sex"] = row[2]
                d["Age"] = row[3]
                d["IP"] = row[4]
                data.append(d)
            res["msg"] = "OK"
            res["data"] = data
        except Exception as err:
            res["msg"] = str(err)
        return res
